package cn.lwx.javaweb.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.lwx.javaweb.bean.StudentInfo;
import cn.lwx.javaweb.utils.JDBCUtils;

/**
 * 处理学生信息工具类
 *
 * @author lwx
 * @date 2021-05-08
 */
public class StudentUtils {

    // 插入
    public static int insert(StudentInfo student) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int n = 0;

        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into t_studentinfo(id,s_name,s_class,s_no,s_college,s_sex) values(?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, null);
            ps.setString(2, student.getsName());
            ps.setString(3, student.getsClass());
            ps.setString(4, student.getsNo());
            ps.setString(5, student.getsCollege());
            ps.setString(6, student.getsSex());
            n = ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.资源释放——完成后关闭
            JDBCUtils.close(rs, ps, conn);
        }
        return n;
    }

    // 修改
    public static int update(String id, String sName, String sClass, String sNo, String sCollege, String sSex) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();

            String sql = "update t_studentinfo set s_name=? ,s_class=? ,s_no=? ,s_college=? ,s_sex=? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,sName);
            ps.setString(2,sClass);
            ps.setString(3,sNo);
            ps.setString(4,sCollege);
            ps.setString(5,sSex);
            ps.setString(6,id);

            n = ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.资源释放——完成后关闭
            JDBCUtils.close(rs, ps, conn);
        }
        return n;
    }


    // 删除
    public static int remove(String id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "delete from t_studentinfo where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);
            n = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.资源释放——完成后关闭
            JDBCUtils.close(rs, ps, conn);
        }
        return n;
    }


    // 查询全部学生信息
    public static List<StudentInfo> queryAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        StudentInfo student = null;
        List<StudentInfo> list = new ArrayList<StudentInfo>();

        try {
            conn = JDBCUtils.getConnection();

            //3.创建statement
            stmt = conn.createStatement();

            //4.执行语句
            String sql = "SELECT id,s_name,s_class,s_no,s_college,s_sex FROM t_studentinfo";

            rs = stmt.executeQuery(sql);

            //5.循环遍历——展开结果集数据库
            while (rs.next()) {
                // 通过字段检索
                int id = rs.getInt(1);
                String sName = rs.getString(2);
                String sClass = rs.getString(3);
                String sNo = rs.getString(4);
                String sCollege = rs.getString(5);
                String sSex = rs.getString(6);
                student = new StudentInfo(id, sName, sClass, sNo, sCollege, sSex);
                list.add(student);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.资源释放——完成后关闭
            JDBCUtils.close(rs, stmt, conn);
        }
        return list;
    }

    // 根据id查询学生信息
    public static StudentInfo queryById(String sid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StudentInfo student = null;

        try {
            conn = JDBCUtils.getConnection();

            String sql = "SELECT id,s_name,s_class,s_no,s_college,s_sex FROM t_studentinfo where id = ?";

            ps = conn.prepareStatement(sql);

            ps.setString(1,sid);

            rs = ps.executeQuery();

            // 这个结果集当中只有一条数据，不需要while循环
            if (rs.next()) {
                int id = rs.getInt(1);
                String sName = rs.getString(2);
                String sClass = rs.getString(3);
                String sNo = rs.getString(4);
                String sCollege = rs.getString(5);
                String sSex = rs.getString(6);
                student = new StudentInfo(id, sName, sClass, sNo, sCollege, sSex);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.资源释放——完成后关闭
            JDBCUtils.close(rs, ps, conn);
        }
        return student;
    }

    // 根据s_name查询学生信息
    public static List<StudentInfo> queryByName(String name) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StudentInfo student = null;
        List<StudentInfo> list = new ArrayList<StudentInfo>();

        try {
            conn = JDBCUtils.getConnection();

            String sql = "SELECT id,s_name,s_class,s_no,s_college,s_sex FROM t_studentinfo where s_name like ?";

            ps = conn.prepareStatement(sql);

            ps.setString(1,"%"+name+"%");

            rs = ps.executeQuery();

            while (rs.next()) {
                int id = rs.getInt(1);
                String sName = rs.getString(2);
                String sClass = rs.getString(3);
                String sNo = rs.getString(4);
                String sCollege = rs.getString(5);
                String sSex = rs.getString(6);
                student = new StudentInfo(id, sName, sClass, sNo, sCollege, sSex);
                list.add(student);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.资源释放——完成后关闭
            JDBCUtils.close(rs, ps, conn);
        }
        return list;
    }

}
